from datetime import datetime

from mate.config import params
import pandas as pd
from utils import file_utils
from clean_data.merge_excel import adjust_column_width
from mate.config.params import column_widths
import warnings
from config import shanlian_config
from h3yun.service import medical_goods_servce, site_data_service, goods_service, sales_data_service
from config.excel_config import season_sales_volume_column_name, specialist_column_name, manager_column_name
from config.mate_config import season_name
warnings.filterwarnings('ignore')


# 陆艳的需求
# F0000001 出库时间
# F0000003 配送商备案产品
# F0000002 配送商
# F0000004 生产厂家
# F0000005 医院名称
# F0000006 数量
# F0000007 单价
# F0000008 批号
# F0000010 数据来源
# F0000011 金额
# F0000014 产品编码公司内部
def clean_data(file_path=params.total_excel_file_path):
    total_excel_column_types = {
        'F0000013': str,
        'F0000014': str,
        'F0000015': str,
        'F0000016': str
    }
    df = pd.read_excel(file_path, dtype=total_excel_column_types)
    # 先按照闪链的规则清洗掉血气产品
    condition = ((df['F0000003'] == '血气测定试剂盒（电极法）Measurement Cartridge')
                 & (df['F0000002'] == '众志飞救医疗科技（深圳）有限公司')
                 & (df['F0000010'] == '众志--闪链配送'))
    df = df[~condition]

    # 清洗金博的数据 找到
    # 配送商是广西南宁柳药 产品是 心肌肌钙蛋白I/肌红蛋白/肌酸激酶同工酶联合检测试剂盒（荧光免疫层析法）
    # 终端医院改成 隆安县中医院 单价改成 2500
    df['F0000005'] = df.apply(jinbo_clean_condition, axis=1)
    df['F0000007'] = df.apply(jinbo_clean_condition2, axis=1)
    df['F0000011'] = df.apply(jinbo_clean_condition3, axis=1)

    # 去除高值数据
    # df = df[~df['F0000003'].isin(get_filter_list())]
    # # 去除掉闪链其它不是试剂的数据
    # df = df[~df['F0000003'].isin(get_shanlian_list())]
    df = df[df['F0000014'].astype(str).str.match(r'^01\d{5}$')]

    test_file_path = f"{params.total_excel_file_prefix}\\清洗后的数据.xlsx"
    file_utils.delete_file(test_file_path)
    df.to_excel(f"{params.total_excel_file_prefix}\\清洗后的数据.xlsx", index=False)

    # 读取 Excel 文件 a(日更流向数据汇总表清洗过的)
    df_a = pd.read_excel(test_file_path, dtype=total_excel_column_types)

    # 读取 Excel 文件 b(专员负责的医院)
    df_b = pd.read_excel(params.target_excel_file_path)

    # 合并两个 DataFrame，根据共同的列名进行合并
    # 这里是根据医院匹配清洗后的数据的结果
    merged_df = pd.merge(df_a, df_b, left_on='F0000005', right_on='医院名称')

    # 这里是单独拿出 广州派翠克商贸有限公司 和  郑州安图科技发展有限公司 的流向数据
    special_df = handle_special_characters(df_a)

    result_df = pd.concat([merged_df, special_df], ignore_index=True)

    year = ['2024'] * len(result_df)
    month = []
    season = ['2024Q2'] * len(result_df)
    for index, value in result_df['F0000001'].items():
        month.append(str(int(value[5:7]))+'月')

    # 匹配 配送商点数(商务要用) 氚云备案单价
    pss_point, CYPADJ = handle_mate_PSS_point(result_df['F0000002'], result_df['F0000005'], result_df['F0000014'])
    # 氚云上面的配送点可能会变化 要去变更表里面在匹配一次
    pss_point = handle_mate_change_PSS_point(result_df['F0000002'], result_df['F0000005'], result_df['F0000014'], result_df['F0000001'], pss_point,CYPADJ)
    # 计算氚云备案单价和配送商单价的差值
    price_diff = []
    for i in range(len(result_df['F0000007'])):
        price_diff.append(CYPADJ[i] - result_df['F0000007'][i])

    PSF = []  # 配送费
    KCPSFHJE=[]  # 扣除配送费后金额
    for i in range(len(result_df['F0000011'])):
        t1 = result_df['F0000011'][i] * (pss_point[i]/100)
        t2 = result_df['F0000011'][i] - t1
        PSF.append(t1)
        KCPSFHJE.append(t2)
    # 配送点要是百分比 换算成小数传到氚云会自动变成百分比
    pss_point = [x / 100 for x in pss_point]
    data = {
        season_sales_volume_column_name['year']: year,
        season_sales_volume_column_name['month']: month,
        season_sales_volume_column_name['CKSJ']: result_df['F0000001'],
        season_sales_volume_column_name['season']: season,
        season_sales_volume_column_name['PSS']: result_df['F0000002'],
        season_sales_volume_column_name['YYMC']: result_df['F0000005'],
        season_sales_volume_column_name['KS']: result_df['科室'],
        season_sales_volume_column_name['CJ']: result_df['F0000004'],
        season_sales_volume_column_name['CPNB_BM']: result_df['F0000014'],
        season_sales_volume_column_name['CPNB_MC']: result_df['F0000003'],
        season_sales_volume_column_name['SL']: result_df['F0000006'],
        season_sales_volume_column_name['price']: result_df['F0000007'],
        season_sales_volume_column_name['amount']: result_df['F0000011'],
        season_sales_volume_column_name['CYPADJ']: CYPADJ,
        season_sales_volume_column_name['PSD']: pss_point,
        season_sales_volume_column_name['PSF']: PSF,
        season_sales_volume_column_name['KCPSFHJE']: KCPSFHJE,
        season_sales_volume_column_name['manager']: result_df['经理'],
        season_sales_volume_column_name['specialist']: result_df['专员名称'],
        season_sales_volume_column_name['CPPC']: result_df['F0000008'],
        season_sales_volume_column_name['ID']: result_df['F0000009'],
        season_sales_volume_column_name['price_diff']: price_diff
    }

    result_df = pd.DataFrame(data)

    # result_df['配送费'] = result_df['金额'] * result_df['配送点']
    # result_df['扣除配送费后金额'] = result_df['金额'] - result_df['配送费']
    result_df.to_excel(params.summary_excel_file_path, index=False)

    adjust_column_width(result_df, params.summary_excel_file_path, column_widths)


# 算每个专员的达标率
def statistic_specialist_index():

    df_a = pd.read_excel(f"{params.summary_excel_file_path}")
    # 只保留专员的名称和金额两列
    df_selected = df_a[['专员', '金额']]
    # 按照专员的名称分组，并将金额列合并（求和）
    df_grouped = df_selected.groupby('专员')['金额'].sum().reset_index()
    # print(df_grouped)

    df_b = pd.read_excel(f"{params.target_excel_file_prefix}\\Q2季度达标要求.xlsx", sheet_name='专员')

    mate_dict = {}
    for i in range(len(df_b['专员'])):
        mate_dict[df_b['专员'][i]] = {
            'index': df_b['指标'][i],
            'manager': df_b['经理'][i],
            'area': df_b['区域'][i]
        }

    # 销售额
    sales_volume = []
    # 指标
    sales_index = []
    # 达标率
    completion_rate = []
    # 经理
    manager = []
    # 区域
    area = []

    # 排名
    rank = []
    for i in range(len(df_grouped['专员'])):
        p = df_grouped['专员'][i]
        s = df_grouped['金额'][i]
        t = mate_dict[p]['index']  # 指标
        m = mate_dict[p]['manager']  # 经理
        a = mate_dict[p]['area']  # 区域
        sales_volume.append(s)
        if int(t) != 0:
            # completion_rate.append((s/t*100).round(2))
            completion_rate.append((s / t).round(2)) # 弄成小数的形式
        else:
            completion_rate.append(0)
        sales_index.append(t)
        manager.append(m)
        area.append(a)
    data = {
        specialist_column_name['season']: [season_name]*len(manager),
        specialist_column_name['manager']: manager,
        specialist_column_name['area']: area,
        specialist_column_name['specialist']: df_grouped['专员'],
        specialist_column_name['sales_index']: sales_index,
        specialist_column_name['sales_volume']: sales_volume,
        specialist_column_name['completion_rate']: completion_rate
    }
    result_df = pd.DataFrame(data)

    # 添加排名列，按照完成率排序，排名从1开始
    result_df['排名'] = result_df[specialist_column_name['completion_rate']].rank(ascending=False, method='min').astype(int)

    # 按照排名列进行排序
    result_df = result_df.sort_values(by='排名')

    # 重置索引
    result_df = result_df.reset_index(drop=True)

    result_df.to_excel(f"{params.specialist_excel_file_path}", index=False)


# 算每个经理的达标率
def statistic_manager_index():
    df_a = pd.read_excel(f"{params.specialist_excel_file_path}")
    df_b = pd.read_excel(f"{params.target_excel_file_prefix}\\Q2季度达标要求.xlsx", sheet_name='经理')
    mate_dict = {}
    for i in range(len(df_b['经理'])):
        mate_dict[df_b['经理'][i]] = {
            'area': df_b['代表区域'][i],
            'sales_index': df_b['指标'][i]
        }
    df_selected = df_a[['经理', '销售额']]
    df_grouped = df_selected.groupby('经理')['销售额'].sum().reset_index()

    area = []
    sales_index = []
    completion_rate = []
    total_index = 0
    total_sales = 0
    for i in range(len(df_grouped['经理'])):
        # 经理
        m = df_grouped['经理'][i]
        # 销售额
        s = df_grouped['销售额'][i]
        total_sales += float(s)
        # 代表区域
        area.append(mate_dict[m]['area'])
        # 指标
        t = mate_dict[m]['sales_index']
        total_index += float(t)
        sales_index.append(t)
        if int(t) != 0:
            # completion_rate.append((s / t * 100).round(2))
            completion_rate.append((s / t).round(2)) # 弄成小数的形式
        else:
            completion_rate.append(0)
    # 保留两位小数
    # avg = round(total_sales / total_index*100, 2)
    avg = round(total_sales / total_index, 2)
    avg_completion_rate = [avg]*len(df_grouped['经理'])
    data = {
        manager_column_name['season']: [season_name] * len(area),
        manager_column_name['manager']: df_grouped['经理'],
        manager_column_name['area']: area,
        manager_column_name['sales_index']: sales_index,
        manager_column_name['sales_volume']: df_grouped['销售额'],
        manager_column_name['completion_rate']: completion_rate,
        manager_column_name['avg_completion_rate']: avg_completion_rate
    }
    result_df = pd.DataFrame(data)
    result_df.to_excel(f"{params.manager_excel_file_path}", index=False)


# 处理两家特殊的公司流向 广州派翠克商贸有限公司 和 郑州安图科技发展有限公司
def handle_special_characters(df):
    filter_df = df[(df['F0000005'] == '广州派翠克商贸有限公司') | (df['F0000005'] == '郑州安图科技发展有限公司')]

    manger = []
    specialist = []
    for temp in filter_df['F0000005']:
        if temp == '广州派翠克商贸有限公司':
            manger.append('何康')
        else:
            manger.append('')
        specialist.append(temp)
    filter_df['经理'] = manger
    filter_df['专员名称'] = specialist
    return filter_df


# F0000049 销售客户名称/医院编码
# F0000051 销售终端医院编码
# D117400Fnczoagqa7nmbvn0erffeps704.F0000045 终端医院供货价
def handle_mate_PSS_point(PSS,YYMC,CP_CODE):
    goods_list = medical_goods_servce.find_medical_goods_data()
    site_dict = get_site_dict()
    pss_point = []
    CYPADJ = []
   # print(goods_list[0])
    for i in range(len(PSS)):
        pss_code = site_dict[PSS[i]]
        yymc_code = site_dict[YYMC[i]]
        cp_code = CP_CODE[i]
      #  print(f'{PSS[i]} {pss_code} - {yymc_code} - {cp_code}')
        res = 0
        for goods in goods_list:
            if goods.get('Status') != 1:
                continue
            if ((goods['F0000049'] == pss_code and goods['F0000051'] == yymc_code)
                    or (goods['F0000049'] == goods['F0000051'] and goods['F0000049'] == yymc_code)):
                medical_goods_list = goods['D117400Fnczoagqa7nmbvn0erffeps704']
                flag = 0
                for medical_goods in medical_goods_list:
                    if medical_goods['F0000026'] == cp_code:
                        pss_point.append(medical_goods['F0000044'])
                        CYPADJ.append(medical_goods['F0000045'])
                        flag = 1
                        res = 1
                        break
                if flag == 1:
                    break
        if res == 0:
            pss_point.append(0)
            CYPADJ.append(0)
    return pss_point, CYPADJ


# 配送点可能会变更,处理变更之后的配送点
# D117400Fbslm7kwhysem74i3jdcu3zl47.F0000070 变更之后配送点
# D117400Fbslm7kwhysem74i3jdcu3zl47.F0000078 变更后-执行日期
# D117400Fbslm7kwhysem74i3jdcu3zl47.F0000026 产品编号
# D117400Fbslm7kwhysem74i3jdcu3zl47.F0000045 终端医院供货价（元）
def handle_mate_change_PSS_point(PSS, YYMC, CP_CODE, CKSJ, pss_point, CYBADJ):
    goods_list = medical_goods_servce.find_medical_changes_goods_data()
    site_dict = get_site_dict()
    # print(goods_list[0])
    for i in range(len(PSS)):
        pss_code = site_dict[PSS[i]]
        yymc_code = site_dict[YYMC[i]]
        cp_code = CP_CODE[i]
        #  print(f'{PSS[i]} {pss_code} - {yymc_code} - {cp_code}')
        PSD_list = []
        for goods in goods_list:
            if goods.get('Status') != 1:
                continue
            if ((goods['F0000049'] == pss_code and goods['F0000051'] == yymc_code)
                    or (goods['F0000049'] == goods['F0000051'] and goods['F0000049'] == yymc_code)):
                medical_goods_list = goods['D117400Fbslm7kwhysem74i3jdcu3zl47']
                for medical_goods in medical_goods_list:
                    # 原始日期字符串
                    date_str = medical_goods['F0000078']
                    # if not date_str:
                    #      print(f"配送商{pss_code} 医院名称{yymc_code} 产品编码{medical_goods['F0000026']}")
                    if date_str is None:
                        continue
                    # 解析原始日期字符串
                    date_obj = datetime.strptime(date_str, "%Y/%m/%d %H:%M:%S")
                    # 转换为所需格式
                    new_date_str = date_obj.strftime("%Y-%m-%d")
                    if medical_goods['F0000026'] == cp_code and new_date_str <= CKSJ[i]:
                        PSD_list.append({
                            'date': new_date_str,
                            'CYBDJ': medical_goods['F0000045'],
                            'PSD': medical_goods['F0000070'],
                        })
        if len(PSD_list) != 0:
            sorted_PSD_list = sorted(PSD_list, key=lambda x: x['date'], reverse=True)
            pss_point[i] = sorted_PSD_list[0]['PSD']
            CYBADJ[i] = sorted_PSD_list[0]['CYBDJ']
    return pss_point


def get_site_dict():
    site_dict = {}
    site_list = site_data_service.find_site_info_data()
    for site in site_list:
        site_name = site['F0000011']
        site_code = site['F0000026']
        # if site_name in site_dict:
        #     print(site_name)
        # 广东医科大学附属东莞第一医院 站点里面出现了两次 这个医院名称弄出来的字典 有重复了
        site_dict[site_name] = site_code
        # site_dict[site_code] = site_name
    return site_dict



def get_shanlian_list():
    df = pd.read_excel(shanlian_config.selenium_excel_file_url)
    df = df[df['医疗器械注册证号'] == "非医疗器械"]
    return df['产品名称']


# 修改医院
def jinbo_clean_condition(row):

    if (row['F0000003'] == '心肌肌钙蛋白I/肌红蛋白/肌酸激酶同工酶联合检测试剂盒（荧光免疫层析法）'
            and row['F0000002'] == '广西南宁柳药药业有限公司'
            and row['F0000010'] == '众志---金博'):
        return '隆安县中医院'
    else:
        return row['F0000005']


# 修改单价
def jinbo_clean_condition2(row):

    if (row['F0000003'] == '心肌肌钙蛋白I/肌红蛋白/肌酸激酶同工酶联合检测试剂盒（荧光免疫层析法）'
            and row['F0000002'] == '广西南宁柳药药业有限公司'
            and row['F0000010'] == '众志---金博'):
        return '2500'
    else:
        return row['F0000007']


# 修改总金额
def jinbo_clean_condition3(row):

    if (row['F0000003'] == '心肌肌钙蛋白I/肌红蛋白/肌酸激酶同工酶联合检测试剂盒（荧光免疫层析法）'
            and row['F0000002'] == '广西南宁柳药药业有限公司'
            and row['F0000010'] == '众志---金博'):
        return 2500 * float(row['F0000006'])
    else:
        return row['F0000011']


if __name__ == '__main__':
    clean_data()
    # statistic_specialist_index()
    # statistic_manager_index()

    # sales_data_service.upload_season_sales_volume_data()
    # sales_data_service.upload_season_specialist_data()
    # sales_data_service.upload_season_manager_data()

